Complex software for business and financial applications is usually accessed from a terminal on an employee's desk. The employee's terminal or workstation generally functions as client in a client-server environment. That is, the employee's terminal (client) is running some software locally, but depends on other software and data on another computer (server) on a network, or on a network of servers.
Typically large sets of data needed by an application are contained in files managed and queried by a database program. Many large software applications, including custom built programs, interface directly to commercial database programs. The user of the high level business software is generally unaware of the software interface to the database program.
The information in databases is stored in computer files. The information is typically further subdivided into records containing sets of related information in fields. Each field typically contains single data element. At a most basic level, the database can establish key fields for quickly accessing certain records. These basic database operations are managed by the database program and function autonomously of the calling application.
The calling application, such as a custom financial trading program for a financial institution, interacts with the database through a high level database interaction language called a structured query language (SQL). Individual SQL commands representing queries to the database resemble programming lines of high level programming languages. Generally several lines of SQL code are needed to fulfill a given database operation such as a database query. The set of SQL commands might be used over and over again. It can be saved as a set under one name as a “procedure”. A saved set is called a stored procedure. The stored procedure can be saved as a compiled file by the database program for fast execution. In practice stored procedures are slightly modified SQL queries that are formed into functions and loaded into the database. They are executed using input parameters, and in return, supply a result set of data.
High level application programs that make repeated calls to databases can make use of many stored procedures. Since the stored procedures are sections of executable computer code, they use computer resources, largely by causing reads and writes to the database via the database supervisory program along with the associated execution times. The stored procedures are almost always run with input parameters. The particular input parameters invoked for a given stored procedure call are supplied by the application program. These parameters may be copies of user entered data, derived, or calculated by the application program. The parameters input to a stored procedure may vary widely in the application's actual real-time operating environment.
Stored procedures may remain static, or they may be modified during the life of a given version of the business or financial application program. New versions of the application might introduce new or modified stored procedures. It is crucial to test applications with modified or new stored procedures as well as new applications using existing stored procedures, to ensure that the procedures are performing correctly. One measure of correct operation of the overall application is the time of execution for the applications stored procedures.
One way to measure stored procedure execution performance, is to query the database application itself, after a called stored procedure is executed. The supervisory database can generally return statistics associated with a particular stored procedure execution. The most significant statistics are the number of reads and writes that a procedure causes in the database. The time of execution from stored procedure call to the time of returned data is also interesting, but somewhat less important since it is more a function of the speed of the network at that moment, and the performance of various hardware platforms than strictly a measure of the procedure performance itself.
Most stored procedures incorporate various execution paths. The particular combination of execution paths that runs for a given stored procedure call depends on the parameters passed to the stored procedure on that particular call. FIG. 1A shows the simplest case of a stored procedure with only one execution path that runs the same way regardless of any input parameter. FIG. 1B shows the concept of an execution path set by an input parameter, this procedure having only one “IF” choice of two executable paths. And, finally FIG. 1C shows the more common scenario where a tree of possible executable paths exists within a stored procedure.
Manual testing is unlikely to be able to characterize many permutations of the possible number of execution paths through a stored procedure. And, it is impractical, if not impossible, to verify all executable paths for all combinations of input parameters. Therefore, it would be highly desirable to implement a method for the automatic return of all stored procedure execution statistics, including database writes and reads, for all procedures called by an application in its real-time running environment. While this still might not test all possible permutations of execution paths, it would at least exercise most paths run in the high level application's normal operating environment.